Use master
go
Drop database SMQuiz
go
Create database SMQuiz
go
use SMQuiz
go

Create table Roles
(
	RoleID int identity(1,1) constraint pk_Roles primary key,
	RoleName nvarchar (100),
	Description nvarchar(1000)
)

create table Users
(
	UserID int identity(1,1) constraint pk_Users primary key,
	UserName nvarchar (100),
	DisplayName nvarchar(100),
	Imei nvarchar(100),
	Description nvarchar(1000),
	Password nvarchar(100),
	Email nvarchar(100),
	RoleID  int constraint FK_Users_Roles foreign key references Roles (RoleID)
)

Create table Apps
(
	AppID int identity(1,1) constraint pk_Apps primary key,
	AppName nvarchar (100),
	DisplayName nvarchar(100),
	Description nvarchar(1000),
	StartDate datetime
)


Create table Suppliers
(
	 SupplierID int not null constraint pk_Suppliers primary key, 
	 SupplierName nvarchar(100)
);

Create table Platforms
(
	PlatformID int identity(1,1) constraint pk_Platforms primary key,
	PlatformName nvarchar (100),
	SupplierID  int constraint FK_Suppliers_Platforms foreign key references Suppliers (SupplierID)
);

Create table Apps_Plats
(
	PlatformID int constraint FK_Apps_Plats_Plats foreign key references Platforms (PlatformID),
	AppID int constraint FK_Apps_Plats_Apps foreign key references Apps (AppID),	
	constraint PK_Apps_Plats primary key(PlatformID,AppID)
)

Create table Languages
(
	LanguageID int identity(1,1) constraint pk_Languages primary key,
	LanguageName nvarchar (100),
	Link nvarchar(100)
)


Create table Categories
(
	CategoryID int identity(1,1) constraint pk_Categories primary key,
	CategoryName nvarchar (100),
	Tags nvarchar(500),
	Description nvarchar(1000),
	LanguageID  int constraint FK_Categories_Languages foreign key references Languages (LanguageID),
	UserID  int constraint FK_Categories_Users foreign key references Users (UserID)
)

Create table Quizzes
(
	QuizID int identity(1,1) constraint pk_Quizes primary key,
	QuizName nvarchar (100),
	Tags nvarchar(500),
	Description nvarchar(1000),
	UserID  int constraint FK_Quizzes_Users foreign key references Users (UserID),
	CategoryID int Constraint FK_Quizzes_Categorie foreign key references Categories (CategoryID),
	Media nvarchar(1000),
	Attemps int,
	Createdate datetime,
	isPrivate int,
	DownloadCount int
)

Create table Likes
(
	QuizID  int constraint FK_Likes_Quizzes foreign key references Quizzes (QuizID),
	UserID  int constraint FK_Likes_Users foreign key references Users (UserID),
	CreateDate datetime,
	constraint pk_Likes primary key(UserID,QuizID)
)

Create table Reviews
(
	QuizID  int constraint FK_Reviews_Quizzes foreign key references Quizzes (QuizID),
	UserID  int constraint FK_Reviews_Users foreign key references Users (UserID),
	CreateDate datetime,
	Rate int,
	constraint pk_Reviews primary key(UserID,QuizID)
)
Create table Apps_Quizzes
(
	App_Quize_ID int identity(1,1) constraint pk_Apps_Quizzes primary key,
	AppID int Constraint FK_Apps_Quizzes_Apps foreign key references Apps (AppID),
	QuizID int Constraint FK_Apps_Quizzes_Quizzes foreign key references Quizzes (QuizID),
	Likes int default (0)
)

Create table Questions
(
	QuestionID int identity(1,1) constraint pk_Questions primary key,
	QuestionName nvarchar (1000),
	Description nvarchar(1000),
	Tags nvarchar(500),
	Media nvarchar(1000),
	Likes int default (0),
	Wiki nvarchar(500),
	UserID  int constraint FK_Questions_Users foreign key references Users (UserID),
	QuizID int Constraint FK_Questions_Quizzes foreign key references Quizzes (QuizID)
)

Create table Choices
(
	ChoiceID int identity(1,1) constraint pk_Choices primary key,
	ChoiceName nvarchar (1000),
	Description nvarchar(1000),
	IsCorrect int,
	Media nvarchar(1000),
	QuestionID int Constraint FK_Choices_Question foreign key references Questions (QuestionID)
)

Create table Wiki
(
	WikiID int identity(1,1) constraint pk_Wiki primary key,
	QuestionID int Constraint FK_Wiki_Question foreign key references Questions (QuestionID),
	ChoiceID int Constraint FK_Wiki_Choice foreign key references Choices (ChoiceID),
	Wiki nvarchar(1000),
	Url nvarchar(500)
)

Create table Apps_Quests
(
	App_Quest_ID int identity(1,1) constraint pk_Apps_Quests primary key,
	AppID int Constraint FK_Apps_Quests_Apps foreign key references Apps (AppID),
	QuestionID int Constraint FK_Apps_Quests_Quests foreign key references Questions (QuestionID)
)

--Cho phep luu mot nguoi choi nhieu lan.
Create table Test
(
	TestID int identity(1,1) constraint pk_Test primary key,
	AppID int not null Constraint FK_Test_Apps foreign key references Apps (AppID),
	UserID int not null constraint FK_Test_Users foreign key references Users (UserID),
	StartTime datetime,
	EndTime datetime,
	Score numeric (10,2)
)

Create table Test_Question
(
	Test_Question_ID int identity(1,1) constraint pk_Test_Question primary key,
	TestID int not null Constraint FK_Test_Question_Test foreign key references Test (TestID),
	QuestionID int not null Constraint FK_Test_Question_Quests foreign key references Questions (QuestionID),
	ChoiceID int Constraint FK_Test_Question_Choices foreign key references Choices (ChoiceID),
	IsCorrect int
)

Create table Config
(
	ConfigID int identity(1,1) constraint pk_Configs primary key,
	ConfigName nvarchar (100),
	AppID int not null Constraint FK_Config_Apps foreign key references Apps (AppID),
	Description nvarchar(1000),
	ConfigValue int,
)